package ee.helmesWorkTest.backend.model.dao.postgresqlimplementations;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import ee.helmesWorkTest.backend.model.dao.SectorDAO;
import ee.helmesWorkTest.backend.model.data.Sector;
import ee.helmesWorkTest.backend.database.ConnectionManager;

public class SectorDAOPostgresqlImpl implements SectorDAO
{
	Logger logger = Logger.getLogger(SectorDAOPostgresqlImpl.class);
	Connection dbConnection;
	String sql;
	PreparedStatement preparedStatement;
	Statement statement;
	ResultSet resultSet;
	private final int WARNING_NO_ROWS_AFFECTED = 0;
	private int rowsAffected;
	
	public SectorDAOPostgresqlImpl(Connection dbConnection)
	{
		this.dbConnection = dbConnection;
	}
	
	@Override
	public int insertSector(Sector sector)
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "INSERT INTO Sectors " +  
				"(sector_name, parent_general_sector_id, level_in_hierarchy) " +  
				"VALUES(?, ?, ?);";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			preparedStatement.setString	(1, sector.getSectorName());
			preparedStatement.setObject	(2, sector.getParentGeneralSector(), Types.INTEGER);
			preparedStatement.setInt	(3, sector.getLevelInHierarchy());
			
			logger.info(preparedStatement.toString());
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				resultSet = preparedStatement.getGeneratedKeys();
				resultSet.next();
				int newId = resultSet.getInt(1); 
				sector.setSectorID(newId);
				return rowsAffected;
				
			}
			else
			{
				System.out.println("No rows affected!");
				logger.info("Unsuccessful insertion to a database: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
	@Override
	public int insertSectorInitialData(Sector sector)
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "INSERT INTO Sectors " +  
				"(sector_id, sector_name, parent_general_sector_id, level_in_hierarchy) " +  
				"VALUES(?, ?, ?, ?);";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			preparedStatement.setInt	(1, sector.getSectorID());
			preparedStatement.setString	(2, sector.getSectorName());
			preparedStatement.setObject	(3, sector.getParentGeneralSector(), Types.INTEGER);
			preparedStatement.setInt	(4, sector.getLevelInHierarchy());
			
			logger.info(preparedStatement.toString());
			rowsAffected = preparedStatement.executeUpdate();
			
			if (rowsAffected == 1)
			{
				return rowsAffected;
				
			}
			else
			{
				System.out.println("No rows affected!");
				logger.info("Unsuccessful insertion to a database: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public Sector getSectorByID(int sectorID)
	{
		resultSet = null;
		Sector sectorRetrieved = null;
		sql = "SELECT * FROM Sectors WHERE sector_id = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			
			preparedStatement.setInt(1, sectorID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			resultSet.beforeFirst();
			
			if (resultSet.next())
			{
				sectorRetrieved = fillSectorWithDataFromDatabase();
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return sectorRetrieved;
	}
	
	@Override
	public List<Sector> getAllSectors() 
	{
		resultSet = null;
		Sector sectorRetrieved = null;
		List<Sector> listOfAllSectorsRetrieved = new ArrayList<Sector>();
		sql = "SELECT * FROM Sectors";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{
				sectorRetrieved = fillSectorWithDataFromDatabase();
				
				listOfAllSectorsRetrieved.add(sectorRetrieved);
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return listOfAllSectorsRetrieved;
	}

	@Override
	public int updateSector(Sector sector) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Sectors " + 
				"SET sector_name = ?, parent_general_sector_id = ?, level_in_hierarchy = ? " + 
				"WHERE sector_id = ?;"; 
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setString	(1, sector.getSectorName());
			preparedStatement.setObject	(2, sector.getParentGeneralSector(), Types.INTEGER);
			preparedStatement.setInt	(3, sector.getLevelInHierarchy());
			preparedStatement.setInt	(4, sector.getSectorID());
			
			rowsAffected = preparedStatement.executeUpdate();
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}
	
	//---------------HELPER METHODS---------------------
	private Sector fillSectorWithDataFromDatabase()
	{
		Sector sectorRetrieved = null;
		try 
		{
			sectorRetrieved = new Sector();
			sectorRetrieved.setSectorID(resultSet.getInt("sector_id"));
			sectorRetrieved.setSectorName(resultSet.getString("sector_name"));
			sectorRetrieved.setParentGeneralSector((Integer) resultSet.getObject("parent_general_sector_id"));
			sectorRetrieved.setLevelInHierarchy(resultSet.getInt("level_in_hierarchy"));
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return sectorRetrieved;
	}
}
